package com.qf.base.utils.excelUtils;

import com.beust.jcommander.internal.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.MediaType;
import org.springframework.util.ReflectionUtils;
import org.springframework.web.multipart.MultipartFile;
import sun.management.resources.agent;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * 数据生成excel并导出
 */
@Slf4j
public class ExcelUtils {
    private final static String EXCEL2003="xls";
    private final static String EXCEL2007="xlsx";
    /**
     * 显示的导出表的标题
     */
    private String title;
    /**
     * 文件名称
     */
    private String fileName;
    /**
     * 导出表的列名
     */
    private String[] rowName;
    /**
     * 数据
     */
    private List<Object[]> dataList=new ArrayList<>();
    /**
     * 解析excel
     */
    public static <T> List<T> readExcel(Class<T> cls, MultipartFile file){
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$")&&!fileName.matches("^.+\\.(?i)(xlsx)$")){
            log.error("上传文件格式不正确");
            throw new RuntimeException("上传文件格式不正确");
        }
        List<T> dataList=new ArrayList<>();
        Workbook workbook=null;
        try {
            InputStream is =file.getInputStream();
            if (fileName.endsWith(EXCEL2007)){
                workbook=new XSSFWorkbook();
            }
            if (fileName.endsWith(EXCEL2003)){
                workbook=new HSSFWorkbook();
            }
            if (workbook!=null) {
                //类映射 注解value-->bean columns
                Map<String, List<Field>> classMap = new HashMap<>();
                List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
                fields.forEach(
                        field -> {
                            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                            if (annotation != null) {
                                String value = annotation.value();
                                if (StringUtils.isBlank(value)) {
                                    return;//return起到的作用合continue是相同的语法
                                }
                                if (!classMap.containsKey(value)) {
                                    classMap.put(value, new ArrayList<>());
                                }
                                //设置为反射可获取
                                ReflectionUtils.makeAccessible(field);
                                classMap.get(value).add(field);
                            }
                        }
                );
                //索引-->columns
                Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);
                //默认读取第一个sheet
                Sheet sheet = workbook.getSheetAt(0);
                boolean firstRow = true;
                for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (i < 3) {
                        firstRow = true;
                    }

                    //首行 提取注解
                    if (firstRow) {
                        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                            Cell cell = row.getCell(j);
                            String cellValue = getCellValue(cell).trim();
                            if (classMap.containsKey(cellValue)) {
                                reflectionMap.put(j, classMap.get(cellValue));
                            }
                        }
                        firstRow = false;
                    } else {
                        //忽略空白行
                        if (row == null) {
                            continue;
                        }
                        try {
                            T t = cls.newInstance();
                            //判断是否为空白行
                            boolean allBlank = true;
                            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                                if (reflectionMap.containsKey(j)) {
                                    Cell cell = row.getCell(j);
                                    String cellValue = getCellValue(cell).trim();
                                    if (StringUtils.isNotBlank(cellValue)) {
                                        allBlank = false;
                                    }
                                    List<Field> fieldList = reflectionMap.get(j);
                                    fieldList.forEach(
                                            x -> {
                                                try {
                                                    handleField(t, cellValue, x);
                                                } catch (Exception e) {
                                                    log.error(String.format("reflect field:%s values:%s exception!", x.getName(), cellValue), e);
                                                }
                                            }
                                    );
                                }
                            }
                            if (!allBlank) {
                                dataList.add(t);
                            } else {
                                log.warn(String.format("row:%s is blank ignore!", i));
                            }
                        } catch (Exception e) {
                            log.error(String.format("parse excel exception!", e));
                        }
                    }
                }
            }
        } catch (Exception e) {
             log.error(String.format("parse excel exception!", e));
        }finally {
           if (workbook!=null){
                   try {
                        workbook.close();
                   }catch (Exception e){
                       log.error(String.format("parse excel exception!", e));
                  }
              }
        }
        return dataList;
    }

    /**
     * 增加一个导出条件sheet页
     * @param workbook 工作文本
     * @param sheetNum  当前sheet页所处位置
     * @param sheetTitle    标题
     * @param data  内容
     */
    public static void writeSheet(Workbook workbook,int sheetNum,String sheetTitle,List<Map<String,String>> data){
        if (MyCollectionUtils.isEmpty(data)){
            return;
        }
        if (StringUtils.isBlank(sheetTitle)){
            sheetTitle="sheet"+sheetNum;
        }
        Sheet sheet=workbook.createSheet();
        sheet.setDefaultColumnWidth(17);
        workbook.setSheetName(sheetNum,sheetTitle);
        //创建首行
        CellStyle cellStyle=workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font=workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);
        Row homeRow=sheet.createRow(0);
        Cell homeCell_0=homeRow.createCell(0);
        homeCell_0.setCellStyle(cellStyle);
        homeCell_0.setCellValue("条件名称");
        Cell homeCell_1=homeRow.createCell(1);
        homeCell_1.setCellStyle(cellStyle);
        homeCell_1.setCellValue("条件值");
        for (int i=0;i<data.size();i++){
            Row row=sheet.createRow(i+1);
            Map<String,String> map=data.get(i);
            if (MyCollectionUtils.isEmpty(map)||!map.containsKey("label")||!map.containsKey("valueName")){
                continue;
            }
            int j=0;
            while (j<2){
                Cell cell=row.createCell(j);
                if (j==0){
                    cell.setCellValue(map.get("label"));
                }else {
                    cell.setCellValue(map.get("valueName"));
                }
                j++;
            }
        }
    }
    public static <T> void writeSheet(Workbook workbook,int sheetNum,String sheetTitle,List<T> dataList,Class<T> cls,List<String> exportFieldList){
        if (StringUtils.isBlank(sheetTitle)){
            sheetTitle="sheet1";
        }

        Field[] fields=cls.getDeclaredFields();
        List<Field> fieldList=Arrays.stream(fields)
                .filter(field -> {
                    ExcelColumn annotation=field.getAnnotation(ExcelColumn.class);
                    if (annotation!=null&&annotation.col()>0){
                        if (exportFieldList!=null){
                            if (exportFieldList.indexOf(field.getName())>-1){
                                ReflectionUtils.makeAccessible(field);
                                return true;
                            }
                        }else {
                            ReflectionUtils.makeAccessible(field);
                            return true;
                        }
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int col=0;
                    ExcelColumn annotation=field.getAnnotation(ExcelColumn.class);
                    if (annotation!=null){
                        col= annotation.col();
                    }
                    return col;
                })).collect(Collectors.toList());
        Sheet sheet=workbook.createSheet();
        sheet.setDefaultColumnWidth(17);
        workbook.setSheetName(sheetNum,sheetTitle);
        AtomicInteger ai =new AtomicInteger();
        {
           Row row=sheet.createRow(ai.getAndIncrement());
           AtomicInteger aj=new AtomicInteger();
           //写入头部
            fieldList.forEach(field -> {
                ExcelColumn annotation=field.getAnnotation(ExcelColumn.class);
                String columnName="";
                if (annotation!=null){
                    columnName= annotation.value();
                }
                Cell cell=row.createCell(aj.getAndIncrement());
                CellStyle cellStyle=workbook.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);

                Font font=workbook.createFont();
                font.setBold(true);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(columnName);
            });
        }
        //写入数据
        if (CollectionUtils.isNotEmpty(dataList)){
            dataList.forEach(t->{
                if (t==null){
                    return;
                }
                Row row1=sheet.createRow(ai.getAndIncrement());
                AtomicInteger aj=new AtomicInteger();
                fieldList.forEach(field -> {
                    Class<?> type=field.getType();
                    Object value="";
                    try {
                        value=field.get(t);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    Cell cell=row1.createCell(aj.getAndIncrement());
                    if (value!=null){
                        if (type==Date.class){
                            cell.setCellValue(value.toString());
                        }else {
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellValue(value.toString());
                    }
                });
            });
        }
        for (int i=0;i<sheet.getRow(0).getPhysicalNumberOfCells();i++){
            sheet.autoSizeColumn((short)i);
        }
        //冻结窗格
        workbook.getSheet(sheetTitle).createFreezePane(0,1,0,1);
    }

    /**
     * 导出单个sheet的excel并下载
     * @param response
     * @param dataList
     * @param cls
     * @param filePath
     * @param <T>
     */
    public static <T> void writeExcel(HttpServletResponse response,List<T> dataList,Class<T> cls,String filePath){
        Workbook wb=new XSSFWorkbook();
        writeSheet(wb,0,"sheet1",dataList,cls,null);
        downFile(response,filePath,wb);
    }

    /**
     * 导出多个sheet的excel并下载--单个class
     * @param response
     * @param cls
     * @param filePath
     * @param sheetMap
     * @param exportFieldList
     * @param <T>
     */
    public static <T> void writeMutlipleSheetExcel(HttpServletResponse response,Class<T> cls,String filePath,LinkedHashMap<String,List> sheetMap,List<String> exportFieldList){
        Workbook wb=new XSSFWorkbook();
        int num=0;
        for (String key: sheetMap.keySet()){
            if (!MyCollectionUtils.isEmpty(sheetMap.get(key))&&!MyCollectionUtils.isEmpty(exportFieldList)){
                writeSheet(wb,num,key,sheetMap.get(key), cls,exportFieldList);
                num++;
            }
        }
        //浏览器下载excel或保存到本地
        downFile(response,filePath,wb);
    }

    /**
     * 导出多个sheet的excel--多个class
     * @param response 导出相应
     * @param clsMap    需要导出的数据对应的类集合
     * @param sheetMap  每个sheet也需要导出的数据
     * @param exportFieldMap    实际需要导出的字段集合
     * @param conditions    导出的条件，如果不为空，且包含label合valueName则在excel最后添加条件
     * @param <T>
     */
    public static <T> void  writeMutlipleSheetExcelByClass(HttpServletResponse response,
                                                            Map<String,Class> clsMap,
                                                            LinkedHashMap<String,List> sheetMap,
                                                            Map<String,List<String>> exportFieldMap,
                                                            List<Map<String,String>> conditions){
        Workbook wb=new XSSFWorkbook();
        int num=0;
        for (String key:sheetMap.keySet()){
            if (!MyCollectionUtils.isEmpty(sheetMap.get(key))&&clsMap.get(key)!=null){
                List<String> exportFieldList=Optional.ofNullable(exportFieldMap).map(model->exportFieldMap.get(key)).orElse(Collections.emptyList());
                writeSheet(wb,num,key,sheetMap.get(key),clsMap.get(key),exportFieldList);
                num++;
            }
        }
        if (!MyCollectionUtils.isEmpty(conditions)){
            writeSheet(wb,num++,"筛选条件", conditions);
        }
        //浏览器保存到本地
        buildExcelDocument("abbot.xlsx", wb, response);
    }

    /**
     * 浏览器下载excel并保存到本地
     * @param response
     * @param filePath
     * @param wb
     */
    public static void downFile(HttpServletResponse response,String filePath,Workbook wb){
        //浏览器下载excel
        if (StringUtils.isNotBlank(filePath)){
            saveFile(filePath,wb);
        }else {
            buildExcelDocument("abbot.xlsx",wb,response);
        }
    }

    /**
     * 生成excel保存到指定路径
     * @param path
     * @param wb
     */
    public static void buildExcelFile(String path,Workbook wb){
        File file=new File(path);
        if (file.exists()){
            file.delete();
        }
        FileOutputStream ins=null;
        try {
            ins=new FileOutputStream(file);
            wb.write(ins);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (ins!=null){
                try {
                    ins.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 保存excel到本地
     * @param filePath
     * @param wb
     */
    public static void saveFile(String filePath,Workbook wb){
        if (StringUtils.isNotBlank(filePath)){
            File file=new File(filePath);
            if (!file.getParentFile().exists()){
                file.getParentFile().mkdir();
            }
            buildExcelFile(filePath,wb);
        }
    }

    /**
     * 浏览器下载excel
     * @param fileName
     * @param wb
     * @param response
     */
    public static void buildExcelDocument(String fileName,Workbook wb,HttpServletResponse response){
        try {
            response.setContentType(MediaType.APPLICATION_ATOM_XML_VALUE);
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName,"utf-8"));
            response.flushBuffer();
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
            //TODO 应该统一拦截异常，并且声明自定义异常返回给前端展示
        }finally {
            try {
                if (wb!=null) {
                    wb.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
    }
    //根据每一行获取对应元素
    private static String getCellValue(Cell cell){
        if (cell==null){
            return "";
        }
        if (cell.getCellTypeEnum() == CellType.NUMERIC){
            if (DateUtil.isCellDateFormatted(cell)){
                return DateUtil.getJavaDate(cell.getNumericCellValue()).toString();
            }else {
                return new BigDecimal(cell.getNumericCellValue()).toString();
            }
        }else if (cell.getCellTypeEnum()==CellType.STRING){
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        }else if (cell.getCellTypeEnum()==CellType.FORMULA){
            switch (cell.getCachedFormulaResultTypeEnum()){
                case NUMERIC:
                    return DateUtil.getJavaDate(cell.getNumericCellValue()).toString();
                case STRING:
                    return StringUtils.trimToEmpty(cell.getStringCellValue());
            }
            return StringUtils.trimToEmpty(cell.getCellFormula());
        }else if (cell.getCellTypeEnum()==CellType.BLANK){
            return "";
        }else if (cell.getCellTypeEnum()==CellType.BOOLEAN){
            return String.valueOf(cell.getBooleanCellValue());
        }else if (cell.getCellTypeEnum()==CellType.ERROR){
            return "ERROR";
        }else {
            return cell.toString().trim();
        }
    }
    //处理字段类型
    private static <T> void handleField(T t,String value,Field field)throws Exception{
        Class<?> type=field.getType();
        if (type==null||type==void.class||StringUtils.isBlank(value)){
            return;
        }
        if (type==Object.class){
            field.set(t,value);
            //数字类型
        }else if (type.getSuperclass()==null || type.getSuperclass()==Number.class){
            if (type==int.class||type==Integer.class){
                field.set(t, NumberUtils.toInt(value));
            }else if (type==long.class||type==Long.class){
                field.set(t,NumberUtils.toLong(value));
            }else if (type==byte.class||type==Byte.class){
                field.set(t,NumberUtils.toLong(value));
            }else if (type==short.class||type==Short.class){
                field.set(t,NumberUtils.toLong(value));
            }else if (type==double.class||type==Double.class){
                field.set(t,NumberUtils.toLong(value));
            }else if (type==float.class||type==Float.class){
                field.set(t,NumberUtils.toLong(value));
            }else if (type==char.class||type==Character.class){
                field.set(t, CharUtils.toChar(value));
            }else if (type==boolean.class){
                field.set(t, BooleanUtils.toBoolean(value));
            }else if (type==BigDecimal.class){
                field.set(t,new BigDecimal(value));
            }
        }else if (type==Boolean.class){
            field.set(t,BooleanUtils.toBoolean(value));
        }else if (type== Date.class){
            field.set(t,value);
        }else if (type==String.class){
            field.set(t,value);
        }else {
            Constructor<?> constructor=type.getConstructor(String.class);
            field.set(t,constructor.newInstance(value));
        }

    }

    /**
     * 设置猎头格式
     * @param workbook
     * @param istitle   是否用于表头，表头需要底色并加粗
     * @return
     */
    public static CellStyle getColumnToStyle(Workbook workbook,boolean istitle){
        CellStyle cellStyle=workbook.createCellStyle();
        //背景颜色
        if (istitle){
            cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        }else {
            cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        }
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置水平方向字体居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直方向字体居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下
        cellStyle.setBorderLeft(BorderStyle.THIN);//左
        cellStyle.setBorderTop(BorderStyle.THIN);//上
        cellStyle.setBorderRight(BorderStyle.THIN);//右
        //设置自动换行
        cellStyle.setWrapText(true);
        //设置字体
        Font font=workbook.createFont();
        if (istitle){
            font.setBold(true);//加粗
        }
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 具有相同列相邻列数据相同合并行的导出excel
     */
    public void export(HttpServletRequest request,HttpServletResponse response,String[] proCode,String[] codes) throws UnsupportedEncodingException {
        //创建工作表对象
        HSSFWorkbook workbook=new HSSFWorkbook();
        //创建工作表
        HSSFSheet sheet = workbook.createSheet();
        //表格标题行
        HSSFRow rowm = sheet.createRow(0);
        HSSFCell cellTitle = rowm.createCell(0);
        //获取列头样式对象
        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
        //单元格样式对象
        HSSFCellStyle style = this.getStyle(workbook);
        HSSFCell currentCell = null;
        //设置标题
        sheet.addMergedRegion(new CellRangeAddress(0,1,0,(rowName.length-1)));
        cellTitle.setCellStyle(columnTopStyle);
        cellTitle.setCellValue(title);
        //定义所需列数
        int columnNum=rowName.length;
        //在索引2的位置创建行（最顶端的行开始的第二行）
        HSSFRow rowRowName=sheet.createRow(2);
        //将列头设置到sheet单元格中
        for (int n=0;n<columnNum;n++){
            //创建列头对应个数的单元格
            HSSFCell cellRowName = rowRowName.createCell(n);
            //设置列头单元格的数据类型
            cellRowName.setCellType(CellType.STRING);
            HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
            //设置列头单元格的值
            cellRowName.setCellValue(text);
            //设置列头单元格样式
            cellRowName.setCellStyle(columnTopStyle);
                {
                    int columnWidth = sheet.getColumnWidth(n) / 256;
                    for (int rowNum=0;rowNum<sheet.getLastRowNum();rowNum++){
                       HSSFRow currentRow;
                      //当前行未被使用过的
                      if (sheet.getRow(rowNum)==null) {
                            currentRow=sheet.createRow(rowNum);
                      }else {
                            currentRow=sheet.getRow(rowNum);
                      }
                      if (null!=currentRow.getCell(n)){
                          currentCell=currentRow.getCell(n);
                          if (null!=currentCell){
                              if (null!=currentCell.getStringCellValue()) {
                                  int length = currentCell.getStringCellValue().getBytes().length;
                                    if (columnWidth<length){
                                        columnWidth=length;
                                    }
                              }
                          }
                      }
                    }
                    if (n==0){
                        sheet.setColumnWidth(n,(columnWidth-2)*256);
                    }else {
                        sheet.setColumnWidth(n,(columnWidth+4)*256);
                    }
                }
            }
        //将查询出的数据设置到sheet对应的单元格中
        for (int i=0;i<=dataList.size()-1;i++){
            //遍历每个对象
            Object[] obj = dataList.get(i);
            //创建所需的行数
            HSSFRow row = sheet.createRow(i + 3);
            for (int j=0;j<obj.length-1;j++){
                //设置单元格的数据类型
                HSSFCell cell=null;
                if (obj[j] !=null &&obj[j].toString().length()>0){
                    cell=row.createCell(j,CellType.STRING);
                    //设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }else {
                    cell=row.createCell(j,CellType.BLANK);
                    cell.setCellValue("");
                }
                //设置单元格样式
                cell.setCellStyle(style);
            }
        }
        //让列宽随着导出的列长自动适应
        for (int colNum=0;colNum<columnNum;columnNum++){
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0;rowNum<sheet.getLastRowNum();rowNum++){
                HSSFRow currentRow;
                //当前存在未被使用过的
                if (sheet.getRow(rowNum)==null){
                    currentRow=sheet.createRow(rowNum);
                }else {
                    currentRow=sheet.getRow(rowNum);
                }
                if (null!=currentRow.getCell(colNum)){
                    currentCell=currentRow.getCell(colNum);
                    if (null!=currentCell){
                        if (currentCell.getCellTypeEnum()==CellType.STRING){
                            if (null!=currentCell.getStringCellValue()){
                                int length = currentCell.getStringCellValue().getBytes().length;
                                if (columnWidth<length){
                                    columnWidth=length;
                                }
                            }
                        }
                    }
                }
            }
            if (colNum==0){
                sheet.setColumnWidth(colNum,(columnWidth-2)*256);
            }else {
                sheet.setColumnWidth(colNum,(columnWidth+4)*256);
            }
        }
        int k=0;
        while (k<dataList.size()){
            Object[] OBJ = dataList.get(k);
            Object OBJJ = OBJ[OBJ.length - 1];
            int datailSize = Integer.parseInt(OBJJ.toString());
            if (datailSize>1){
                for (int m=0;m<codes.length;m++){
                    if (Arrays.asList(proCode).contains(codes[m])){
                        mergeCell(sheet,m,k+3,k+2+datailSize,workbook);
                    }
                }
                k+=datailSize;
            }else {
                k++;
            }
        }
        //导出excel,并下载
        if (workbook!=null){
            try {
//                setResponseHeader(response,request,fileName);
//                workbook.write(response.getOutputStream());
                response.setContentType(MediaType.APPLICATION_ATOM_XML_VALUE);
                response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName,"utf-8"));
                response.flushBuffer();
                workbook.write(response.getOutputStream());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public static HttpServletResponse setResponseHeader(HttpServletResponse response,HttpServletRequest request,String fileName){
            try {

                String agent = request.getHeader("User-Agent").toUpperCase();
                if (agent.contains("MSIE")||agent.contains("Trident")||agent.contains("like Gecko")||agent.contains("LIKE GECKO")){
                fileName=URLEncoder.encode(fileName,"UTF-8");
                }else if (agent.contains("Mozilla")){
                    fileName=new String(fileName.getBytes(StandardCharsets.UTF_8),"ISO8859-1");
                }else {
                    fileName=URLEncoder.encode(fileName,"UTF-8");
                }
                response.reset();
                response.setCharacterEncoding("utf-8");
                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition","attachment;filename="+fileName);
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        return response;
    }

    /**
     * 列数据信息单元格样式
     * @param workbook
     * @return
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook){
        HSSFFont font = workbook.createFont();
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式
        HSSFCellStyle style = workbook.createCellStyle();
        //应用字体再样式中
        style.setFont(font);
        //设置垂直（居中）
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置对齐方式（居中）
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置底边框
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置左边框
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置右边框
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置顶边框
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BROWN.getIndex());
        //自动换行
        style.setWrapText(false);
        return style;
    }
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook){
        //设置字体
        HSSFFont font=workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        font.setBold(true);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式
        HSSFCellStyle style = workbook.createCellStyle();
        //应用字体再样式中
        style.setFont(font);
        //设置垂直（居中）
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置对齐方式（居中）
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置底边框
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置左边框
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置右边框
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置顶边框
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BROWN.getIndex());
        //自动换行
        style.setWrapText(false);
        return style;
    }
    /**
     * 合并单元格
     * @param sheet 要合并单元格的excel 的sheet页
     * @param cellLine  要合并单元格的列
     * @param starRow   要合并单元格所在行的起始下标
     * @param endRow    要合并单元格所在行的终止下标
     * @param workbook  样式对象
     */
    public static void mergeCell(HSSFSheet sheet,int cellLine,int starRow,int endRow,HSSFWorkbook workbook){
        //获取样式单元格
        HSSFCellStyle style=workbook.createCellStyle();
        //获取样式字体
        HSSFFont font = workbook.createFont();
        font.setFontName("Courier New");
        //应用字体再样式中
        style.setFont(font);
        //设置垂直（居中）
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置对齐方式（居中）
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置底边框
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置左边框
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置右边框
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置顶边框
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BROWN.getIndex());
        //获取开始行的数据，以使用后面进行比较
        String s_will = sheet.getRow(starRow).getCell(cellLine).getStringCellValue();
        int count = 0;
        boolean flag = false;
        for (int i=starRow + 1;i<=endRow;i++){
            //设置合并列的某行的格式为字符串
            sheet.getRow(i).getCell(cellLine).setCellType(CellType.STRING);
            //获取合并列的某行的值
            String s_current = sheet.getRow(i).getCell(cellLine).getStringCellValue();
            if (s_will.equals(s_current)){
                s_will = s_current;
                if (flag){
                    //进行合并的区域，开始行与结束行为一行的用法
                    sheet.addMergedRegion(new CellRangeAddress(starRow-count,starRow,cellLine,cellLine));
                    //合并区域后再次获取合并后的一整行
                    HSSFRow row=sheet.getRow(starRow-count);
                    if (null!=sheet.getRow(starRow-count).getCell(cellLine)){
                        //设置合并后某行的格式为字符串
                        sheet.getRow(i).getCell(cellLine).setCellType(CellType.STRING);
                        //获取合并列的某行的值
                        String cellValueTemp = sheet.getRow(starRow-count).getCell(cellLine).getStringCellValue();
                        //创建行
                        HSSFCell cell = row.createCell(cellLine);
                        //赋值
                        cell.setCellValue(cellValueTemp);
                        //样式
                        cell.setCellStyle(style);
                    }
                    count=0;
                    flag=false;
                }
                starRow=i;
                count++;
            }else {
                flag=true;
                s_will=s_current;
            }
            //再次合并，防止之前合并失败
            if (i==endRow&&count>0){
                //进行合并的区域，开始行与结束行为一行的用法
                sheet.addMergedRegion(new CellRangeAddress(starRow-count,starRow,cellLine,cellLine));
                //合并区域后再次获取合并后的一整行
                HSSFRow row=sheet.getRow(starRow-count);
                if (null!=sheet.getRow(starRow-count).getCell(cellLine)){
                    //设置合并后某行的格式为字符串
                    sheet.getRow(i).getCell(cellLine).setCellType(CellType.STRING);
                    //获取合并列的某行的值
                    String cellValueTemp = sheet.getRow(starRow-count).getCell(cellLine).getStringCellValue();
                    //创建行
                    HSSFCell cell = row.createCell(cellLine);
                    //赋值
                    cell.setCellValue(cellValueTemp);
                    //样式
                    cell.setCellStyle(style);
                }
            }
        }
    }

    /**
     * 相同数据合并单元格导出数据
     * @Param [columnNames 表头, column 字段名称, rows 数据, excelName 文件名称, mergeIndex 合并列, response]
     **/
    public static void createSXSSFWorkbookHeteroideus(String[] columnNames, String[] column, List<Map<String, Object>> rows, String excelName, int[] mergeIndex, HttpServletResponse response) {

        SXSSFWorkbook workbook = new SXSSFWorkbook(); // 创建工作薄，相当于一个文件
        SXSSFSheet sheet = workbook.createSheet(); // 创建一个表
        // 标题样式
        CellStyle titleStyle = workbook.createCellStyle();

        // 标题字体
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 12); // 字体大小
        titleFont.setFontName("宋体");
        titleStyle.setFont(titleFont);

        // 表头样式
        CellStyle headerStyle = workbook.createCellStyle();

        headerStyle.setWrapText(true); // 设置多行显示
        //这两句话是表示将表头单元格格式设置为文本型，在后面只要调用-----.setDataFormat(format.getFormat("@"))的方法就可以将数据设置为文本型。
        DataFormat format = workbook.createDataFormat();
        headerStyle.setDataFormat(format.getFormat("@"));
        // 表头字体
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setFontName("宋体");
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        // 数据样式
        CellStyle dataStyle = workbook.createCellStyle();
        // 数据字体
        Font dataFont = workbook.createFont();
        dataFont.setFontHeightInPoints((short) 9);
        dataFont.setFontName("宋体");
        dataStyle.setFont(dataFont);

        Row row = null;
        Cell cell = null;
        row = sheet.createRow(0);

        //设置表头
        for (int i = 0; i < columnNames.length; i++) {
            row.setHeight((short) (2 * 256));
            cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(headerStyle);
        }

        List<PoiModel> poiModels = Lists.newArrayList();
        //设置数据行
        for (int i = 0; i < rows.size(); i++) {
            Row dataRow = sheet.createRow(i + 1);

            sheet.trackAllColumnsForAutoSizing();

            Map<String, Object> project = rows.get(i); // 当前行数据

            int index = i + 1; // 当前行

            // 循环字段名称数组
            for (int j = 0; j < column.length; j++) {
                String old = null;

                if (index > 1 && poiModels.size() > 0) {
                    old = poiModels.get(j) == null ? null : poiModels.get(j).getContent();
                }

                // 循环需要合并的列list
                for (int mer : mergeIndex) {
                    // 如果是第一行 为PoiModel赋值记录数据后结束循环
                    if (index == 1) {
                        String oldContent = project.get(column[j]) == null ? "" : project.get(column[j]).toString();
                        String content = project.get(column[j]) == null ? "" : project.get(column[j]).toString();
                        // 为poiModel 赋值
                        PoiModel poiModel = PoiModel.builder().oldContent(oldContent).content(content).rowIndex(1).cellIndex(j).build();
                        poiModels.add(poiModel);
                        break;
                    }
                    // 如果记录list中有值
                    if (poiModels != null && poiModels.size() > 0) {

                        // 对应列 的 PoiModel
                        PoiModel poiModel = poiModels.get(j);
                        // 对应列 的 字段值
                        String content = project.get(column[j]) == null ? "" : project.get(column[j]).toString();

                        // 如果 不是第一列 并且 合并列循环的当前值与该列下标相等
                        if (j > 0 && mer == j) {
                            // 如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把或条件删除
                            //  || poiModel.getContent().equals(content) && !poiModels.get(j - 1).getOldContent().equals(project.get(column[j - 1]))
                            // 如果 当前列，当前行的值与上一行的值不相同，则执行合并  合并以上相同行
                            if (!poiModel.getContent().equals(content)) {
                                get(poiModel, content, index, j, sheet);
                            }
                        }
                        // 处理第一列的情况
                        // 如果 该列是需要合并的，并且是第一列 并且 与上一行的值不同 则合并  合并以上相同行
                        if (mer == j && j == 0 && !poiModel.getContent().equals(content)) {
                            get(poiModel, content, index, j, sheet);
                        }
                        // 最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容
                        // 如果 该列是需要合并的 并且行数为最后一行  并且 poiModel中记录的行数不等于当前行 说明有相同的
                        if (mer == j && index == rows.size() && poiModels.get(j).getRowIndex() != index) {
                            CellRangeAddress cra = new CellRangeAddress(poiModels.get(j).getRowIndex(), index, poiModels.get(j).getCellIndex(), poiModels.get(j).getCellIndex());
                            sheet.addMergedRegion(cra);
                        }
                    }
                }

                Cell dataCell = dataRow.createCell(j);

                Object obj = project.get(column[j]);
                sheet.autoSizeColumn(j, true);

                dataCell.setCellValue(obj == null ? "" : obj.toString());
            }
        }
        //去除掉合并列内容加的唯一标识
        //首先判断是否包含合并的列
        for (int i=0;i<mergeIndex.length;i++){
            //如果包含那么遍历所有的行，进行修改
            for (int j=0;j<sheet.getLastRowNum();j++){
                SXSSFRow row1 = sheet.getRow(j);
                if (Objects.isNull(row1)) {
                    continue;
                }
                //获取合并列的数据
                SXSSFCell cell1 = row1.getCell(mergeIndex[i]);
                String cellValue = cell1.getStringCellValue();
                if (cellValue.contains("+")){
                    cell1.setCellValue(cellValue.substring(0,cellValue.indexOf("+")));
                }
            }
        }


        OutputStream os = null;
        try {
            // 创建一个普通输出流
            os = response.getOutputStream();
            // 请求浏览器打开下载窗口
            response.reset();
            response.setCharacterEncoding("UTF-8");

            excelName = new String(excelName.getBytes(), "ISO8859-1");
            response.setHeader("Content-Disposition", "attachment; filename=" + excelName);// 要保存的文件名
            response.setContentType("application/octet-stream");
            workbook.write(os);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static void get(PoiModel poiModel, String content, int index, int i, Sheet sheet) {
        // 如果 PoiModel记录的行数 不是 当前行 则合并
        if (poiModel.getRowIndex() != index - 1) {
            // 相同值第一次出现的行数  到当前行数-1 （下标）  合并开始列  合并结束列
            CellRangeAddress cra = new CellRangeAddress(poiModel.getRowIndex(), index - 1, poiModel.getCellIndex(), poiModel.getCellIndex());
            //在sheet里增加合并单元格
            sheet.addMergedRegion(cra);
        }
        /*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/
        poiModel.setContent(content);
        poiModel.setRowIndex(index);
        poiModel.setCellIndex(i);
    }
    /**
     * 只适用于导出故障-业务统计
     * @param request
     * @param response
     * @param itemList
     * @throws Exception
     */
    public static void  getExcle(HttpServletRequest request, HttpServletResponse response,List<Map<String,Object>> itemList,String fileName )throws Exception{
        try{
            // 创建一个excel对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            OutputStream out = response.getOutputStream();

            // 定义标题以及设置响应头信息
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            // TODO 生成指定的sheet表
            // 创建工作表
            HSSFSheet sheet = workbook.createSheet("业务保障类统计指标统计");
            /***   绑定列头   ****/
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            HSSFRow rowRowName = sheet.createRow(0);
            HSSFRow rowRowName2 = sheet.createRow(1);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 3));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 6));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 11));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 12, 16));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 17, 20));
            // 定义所需列数
            //指定第一行表头位置，并填充数据
            int[] bt1={0,1,2,4,7,12,17};
            String[] bt1Name={"序号","责任单位","设备运行指标","故障运行指标","光传输设备","通信光缆","通信电源设备"};
            String[] bt2Name={"设备总数","设备运行率","故障总时长（分钟）","故障总次数","平均故障处置时长（分钟）","故障总时长（分钟）",
                    "故障次数","平均故障处置时长（分钟）","设备总数","百套光传输设备平均故障数（次）",
                    "故障总时长（分钟）","故障次数","故障平均处置时长（分钟）","光缆总长度（公里）",
                    "百公里光缆平均故障数（次）","故障总时长（分钟）","故障次数","故障平均处置时长（分钟）",
                    "百套电源设备平均故障数（次）"};
            //单元格样式对象
            HSSFCellStyle styleHearer = ExcelUtils.getColumnTopStyleTow(workbook);
            for(int n = 0; n < bt1.length; n++){
                //创建列头对应个数的单元格
                HSSFCell  cellRowName = rowRowName.createCell(bt1[n]);
                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(bt1Name[n]);
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(styleHearer);
            }

            for(int n = 0; n < bt2Name.length; n++){
                //创建列头对应个数的单元格
                HSSFCell  cellRowName = rowRowName2.createCell(n+2);
                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(bt2Name[n]);
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(styleHearer);
            }
            /***   绑定数据   ****/
            //创建所需的行数（从第三行开始写数据）
            //	将查询出的数据设置到sheet对应的单元格中
            //单元格样式对象
            HSSFCellStyle style = ExcelUtils.getStyleTow(workbook);
            for(int i=0;i<itemList.size();i++){
                HSSFRow row = sheet.createRow(i+2);
                List<String> csList=new ArrayList<>();
                csList.add(i+1+"");
//                csList.add(itemList.get(i).getCODE_VAL()+"");
//                csList.add(Double.valueOf(itemList.get(i).getDeviceNumber()).intValue()+"");
//                csList.add(itemList.get(i).getDeviceMRR()+"");
//                csList.add(Double.valueOf(itemList.get(i).getTOTAL_TIME()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getTOTAL()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getTOTAL_AVERAGE()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getSDH_FAILURE_TOTAL_TIME()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getSDH_GZ_TOTAL()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getSDH_DISPOSAL_TIME_AVERAGE()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getSDH_TOTAL()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getSDH_AVERAGE()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getOPTICAL_FAILURE_TOTAL_TIME()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getOPTICAL_GZ_TOTAL()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getOPTICAL_DISPOSAL_TIME_AVERAGE()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getOPTICAL_LENGTH()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getOPTICAL_GZ_AVERAGE()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getPOWER_GZ_TOTAL()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getPOWER_TOTAL()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getPOWER_AVERAGE()).intValue()+"");
//                csList.add(Double.valueOf(itemList.get(i).getPOWER_TOTAL_TIME()).intValue()+"");
                for(int j = 0; j < csList.size(); j++){
                    //设置单元格的数据类型
                    HSSFCell  cell = row.createCell(j);
                    cell.setCellValue(csList.get(j));
                    cell.setCellStyle(style);
                }
            }
            HSSFCell currentCell = null;
            //让列宽随着导出的列长自动适应
            for (int colNum=0;colNum<bt2Name.length+2;colNum++){
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0;rowNum<sheet.getLastRowNum();rowNum++){
                    HSSFRow currentRow;
                    //当前存在未被使用过的
                    if (sheet.getRow(rowNum)==null){
                        currentRow=sheet.createRow(rowNum);
                    }else {
                        currentRow=sheet.getRow(rowNum);
                    }
                    if (null!=currentRow.getCell(colNum)){
                        currentCell=currentRow.getCell(colNum);
                        if (null!=currentCell){
                            if (currentCell.getCellTypeEnum()==CellType.STRING){
                                if (null!=currentCell.getStringCellValue()){
                                    int length = currentCell.getStringCellValue().getBytes().length;
                                    if (columnWidth<length){
                                        columnWidth=length;
                                    }
                                }
                            }
                        }
                    }
                }
                if (colNum==0){
                    sheet.setColumnWidth(colNum,(columnWidth-2)*256*2);
                }else {
                    sheet.setColumnWidth(colNum,(columnWidth-2)*256*2);
                }
            }

            // 最后将整个excel全部写到浏览器
            workbook.write(out);
            out.flush();
            out.close();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /*
     * 列头单元格样式
     */
    public  static HSSFCellStyle getColumnTopStyleTow(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 20);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("仿宋");
        font.setBold(true);
        font.setColor(IndexedColors.BLACK.getIndex());
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        // 设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }
    /**
     * 列数据信息单元格样式
     * @param workbook
     * @return
     */
    public static HSSFCellStyle getStyleTow(HSSFWorkbook workbook){
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 15);
        //设置字体名字
        font.setFontName("仿宋");
        //设置样式
        HSSFCellStyle style = workbook.createCellStyle();
        //应用字体再样式中
        style.setFont(font);
        //设置垂直（居中）
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置对齐方式（居中）
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置底边框
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //设置左边框
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //设置右边框
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //设置顶边框
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色
        style.setTopBorderColor(IndexedColors.BROWN.getIndex());
        //自动换行
        style.setWrapText(false);
        return style;
    }

}
